package com.ar4j.bench.simplejdbc.service;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Service;

import com.ar4j.bench.common.domain.CategoryWithProductsWithItems;
import com.ar4j.bench.common.domain.ICategory;
import com.ar4j.bench.common.domain.IProduct;
import com.ar4j.bench.common.domain.ProductWithItems;
import com.ar4j.bench.common.service.ICategoryService;
import com.ar4j.bench.jdbc.domain.Category;

/**
 * A category service implemented using spring's SimpleJdbcTemplate
 */
@Service
public class CategorySimpleJdbcService extends SimpleJdbcDaoSupport implements ICategoryService {
  private static final String CATEGORY_BY_ID = "SELECT ID, NAME, IMAGE, DESCRIPTION FROM CATEGORY WHERE ID = ?";
  private static final String DELETE_BY_ID = "DELETE FROM CATEGORY WHERE ID = ?";
  private static final String UPDATE_SQL = "UPDATE CATEGORY SET NAME = :name, IMAGE = :image, DESCRIPTION = :description WHERE ID = :id";
  
  private static final RowMapper<Category> MAPPER = ParameterizedBeanPropertyRowMapper.newInstance(Category.class);
  
  @Autowired
  private ProductSimpleJdbcService productService;
  
  private SimpleJdbcInsert insert;
  
  @Override
  public Long addCategory(ICategory category) {
    SqlParameterSource params = new BeanPropertySqlParameterSource(category);
    Number key = insert.executeAndReturnKey(params);
    return key.longValue();
  }
  
  @Override
  public ICategory findById(Long id) {
    return getSimpleJdbcTemplate().queryForObject(CATEGORY_BY_ID, MAPPER, id);
  }
  
  @Override
  public void removeById(Long id) {
    getSimpleJdbcTemplate().update(DELETE_BY_ID, id);
  }
  
  @Override
  public void updateCategory(ICategory category) {
    SqlParameterSource params = new BeanPropertySqlParameterSource(category);
    getSimpleJdbcTemplate().update(UPDATE_SQL, params);
  }
  
  public Long addCategoryWithProductsWithItems(CategoryWithProductsWithItems category) {
    Long categoryId = addCategory(category.getCategory());
    for(ProductWithItems product : category.getProducts()) {
      product.getProduct().setCategoryId(categoryId);
      productService.addProductWithItems(product);
    }
    return categoryId;
  }

  public CategoryWithProductsWithItems findCategoryWithProductsWithItemsById(Long id) {
    CategoryWithProductsWithItems out = new CategoryWithProductsWithItems();
    out.setCategory(findById(id));
    out.setProducts(productService.findProductWithItemsByCategoryId(id));
    return out;
  }

  public void updateCategoryWithProductsWithItems(CategoryWithProductsWithItems category) {
    updateCategory(category.getCategory());
    
    Map<Long, IProduct> fromDb = new HashMap<Long, IProduct>();
    for(IProduct product : productService.findByCategoryId(category.getCategory().getId())) {
      fromDb.put(product.getId(), product);
    }
    
    for(ProductWithItems product : category.getProducts()) {
      if(product.getProduct().getId() == null || !fromDb.containsKey(product.getProduct().getId())) {
        productService.addProductWithItems(product);
      } else {
        productService.updateProductWithItems(product);
        fromDb.remove(product.getProduct().getId());
      }
    }
    
    for(IProduct product : fromDb.values()) {
      productService.removeProductWithItemsById(product.getId());
    }
  }

  public void removeCategoryWithProductsWithItemsById(Long id) {
    for(IProduct product : productService.findByCategoryId(id)) {
      productService.removeProductWithItemsById(product.getId());
    }
    
    removeById(id);
  }

  @Override
  protected void initDao() throws Exception {
    super.initDao();
    
    insert = new SimpleJdbcInsert(getDataSource())
      .withTableName("CATEGORY")
      .usingColumns("NAME", "IMAGE", "DESCRIPTION")
      .usingGeneratedKeyColumns("ID");

  }
  
  @Autowired
  public void setAutowiredDataSource(DataSource dataSource) {
    setDataSource(dataSource);
  }
  
}
